package com.hlw.hr_man.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hlw.hr_man.Model.AskForLeave;
import com.hlw.hr_man.dao.AskForLeaveDao;
import com.hlw.hr_man.util.JDBCUtil;

/**
 * 
 * @ClassName: AskForLeaveDaoImpl
 * @Description: 请假dao层实现类
 * @author 唐明
 * @date 2016年9月13日 下午3:46:27
 *
 */
public class AskForLeaveDaoImpl implements AskForLeaveDao {

	@Override
	public List<AskForLeave> selectEmployeeAflByEid(int eid, int start, int pageSize) {
		StringBuffer sql = new StringBuffer("select hr_askforleave.ask_id,hr_employee.e_number,hr_employee.e_name,hr_askforleave.ask_startdate,");
		sql.append("hr_askforleave.ask_enddate,hr_askforleave.ask_reason,hr_askforleave.ask_state from hr_askforleave,hr_employee where ");
		sql.append("hr_askforleave.e_id=hr_employee.e_id and hr_employee.e_id=? limit ?,?");
		Connection conn = JDBCUtil.getConnection();
		List<AskForLeave> afls = new ArrayList<AskForLeave>();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, eid);
			pstmt.setInt(2, start);
			pstmt.setInt(3, pageSize);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				AskForLeave afl = new AskForLeave();
				afl.setAsk_id(rs.getInt("ask_id"));
				afl.setE_number(rs.getString("e_number"));
				afl.setE_name(rs.getString("e_name"));
				afl.setAsk_startdate(rs.getString("ask_startdate"));
				afl.setAsk_enddate(rs.getString("ask_enddate"));
				afl.setAsk_reason(rs.getString("ask_reason"));
				short state = rs.getShort("ask_state");
				if (state == 0) {
					afl.setAsk_state("申请中");
				}else if (state == 1){
					afl.setAsk_state("未销假");
				} else {
					afl.setAsk_state("已销假");
				}
				afls.add(afl);
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return afls;
	}

	@Override
	public int selectEmployeeAfl(int eid) {
		StringBuffer sql = new StringBuffer("select count(ask_id) afl_count from hr_askforleave where e_id=?");
		Connection conn = JDBCUtil.getConnection();
		int afl_count = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, eid);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				afl_count = rs.getInt("afl_count");
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return afl_count;
	}

	@Override
	public List<AskForLeave> selectEmployeeAflBySearch(String searchType, String searchContent, int start,
			int pageSize) {
		StringBuffer sql = new StringBuffer("select hr_askforleave.ask_id,hr_employee.e_number,hr_employee.e_name,hr_askforleave.ask_startdate,");
		sql.append("hr_askforleave.ask_enddate,hr_askforleave.ask_reason,hr_askforleave.ask_state from hr_askforleave,hr_employee where ");
		sql.append("hr_askforleave.e_id=hr_employee.e_id");
		if (searchType.equals("员工姓名")) {
			sql.append(" and e_name like ?");
		}
		if (searchType.equals("员工编号")) {
			sql.append(" and e_number like ?");
		}
		sql.append(" order by hr_employee.e_number");
		sql.append(" limit ?,?");
		Connection conn = JDBCUtil.getConnection();
		List<AskForLeave> afls = new ArrayList<AskForLeave>();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			if (searchType.equals("查找所有")) {
				pstmt.setInt(1, start);
				pstmt.setInt(2, pageSize);
			} else {
				pstmt.setString(1, "%"+searchContent+"%");
				pstmt.setInt(2, start);
				pstmt.setInt(3, pageSize);
			}
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				AskForLeave afl = new AskForLeave();
				afl.setAsk_id(rs.getInt("ask_id"));
				afl.setE_number(rs.getString("e_number"));
				afl.setE_name(rs.getString("e_name"));
				afl.setAsk_startdate(rs.getString("ask_startdate"));
				afl.setAsk_enddate(rs.getString("ask_enddate"));
				afl.setAsk_reason(rs.getString("ask_reason"));
				short state = rs.getShort("ask_state");
				if (state == 0) {
					afl.setAsk_state("申请中");
				}else if (state == 1){
					afl.setAsk_state("未销假");
				} else {
					afl.setAsk_state("已销假");
				}
				afls.add(afl);
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return afls;
	}

	@Override
	public int selectEmployeeAflCount(String searchType, String searchContent) {
		StringBuffer sql = new StringBuffer("select count(ask_id) afl_count from hr_askforleave,hr_employee where hr_askforleave.e_id=hr_employee.e_id");
		if (searchType.equals("员工姓名")) {
			sql.append(" and e_name like ?");
		}
		if (searchType.equals("员工编号")) {
			sql.append(" and e_number like ?");
		}
		Connection conn = JDBCUtil.getConnection();
		int afl_count = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			if (!searchType.equals("查找所有")){
				pstmt.setString(1, "%"+searchContent+"%");
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				afl_count = rs.getInt("afl_count");
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return afl_count;
	}

	@Override
	public int insertAfl(AskForLeave afl) {
		StringBuffer sql = new StringBuffer("insert into hr_askforleave(ask_id,e_id,ask_startdate,ask_enddate,ask_reason,ask_state,ask_temp) values(?,?,?,?,?,?,?)");
		Connection conn = JDBCUtil.getConnection();
		int temp = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, afl.getAsk_id());
			pstmt.setInt(2, afl.getE_id());
			pstmt.setString(3, afl.getAsk_startdate());
			pstmt.setString(4, afl.getAsk_enddate());
			pstmt.setString(5, afl.getAsk_reason());
			short state = Short.parseShort(afl.getAsk_state());
			pstmt.setShort(6, state);
			pstmt.setString(7, afl.getAsk_temp());
			temp = pstmt.executeUpdate();
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public int updateAfl(int afl_id) {
		StringBuffer sql = new StringBuffer("update hr_askforleave set ask_state=2 where ask_id=?");
		Connection conn = JDBCUtil.getConnection();
		int temp = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, afl_id);
			temp = pstmt.executeUpdate();
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public int deleteAflByEid(int e_id) {
		StringBuffer sql = new StringBuffer("delete from hr_askforleave where e_id=?");
		Connection conn = JDBCUtil.getConnection();
		int temp = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, e_id);
			temp = pstmt.executeUpdate();
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public List<AskForLeave> selectApplyAfl(int start, int pageSize) {
		StringBuffer sql = new StringBuffer("select hr_employee.e_id,hr_askforleave.ask_id,hr_employee.e_number,hr_employee.e_name,hr_askforleave.ask_startdate,");
		sql.append("hr_askforleave.ask_enddate,hr_askforleave.ask_reason,hr_askforleave.ask_state from hr_askforleave,hr_employee where ");
		sql.append("hr_askforleave.e_id=hr_employee.e_id and hr_askforleave.ask_temp='0' order by hr_employee.e_number limit ?,?");
		List<AskForLeave> afls = new ArrayList<AskForLeave>();
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, start);
			pstmt.setInt(2, pageSize);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				AskForLeave afl = new AskForLeave();
				afl.setE_id(rs.getInt("e_id"));
				afl.setAsk_id(rs.getInt("ask_id"));
				afl.setE_number(rs.getString("e_number"));
				afl.setE_name(rs.getString("e_name"));
				afl.setAsk_startdate(rs.getString("ask_startdate"));
				afl.setAsk_enddate(rs.getString("ask_enddate"));
				afl.setAsk_reason(rs.getString("ask_reason"));
				short state = rs.getShort("ask_state");
				if (state == 0) {
					afl.setAsk_state("申请中");
				}else if (state == 1){
					afl.setAsk_state("未销假");
				} else {
					afl.setAsk_state("已销假");
				}
				afls.add(afl);
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return afls;
	}

	@Override
	public int selectApplyAflCount() {
		StringBuffer sql = new StringBuffer("select count(ask_id) afl_count from hr_askforleave where ask_temp='0' ");
		Connection conn = JDBCUtil.getConnection();
		int afl_count = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				afl_count = rs.getInt("afl_count");
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return afl_count;
	}

	@Override
	public int agreeAflApplyByAskId(int ask_id) {
		StringBuffer sql = new StringBuffer("update hr_askforleave set ask_state=1,ask_temp=1 where ask_id=?");
		Connection conn = JDBCUtil.getConnection();
		int temp = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, ask_id);
			temp = pstmt.executeUpdate();
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}

	@Override
	public List<AskForLeave> selectAflApplyBySearch(String searchType, String searchContent, int start, int pageSize) {
		StringBuffer sql = new StringBuffer("select hr_employee.e_id,hr_askforleave.ask_id,hr_employee.e_number,hr_employee.e_name,hr_askforleave.ask_startdate,");
		sql.append("hr_askforleave.ask_enddate,hr_askforleave.ask_reason,hr_askforleave.ask_state from hr_askforleave,hr_employee where ");
		sql.append("hr_askforleave.e_id=hr_employee.e_id and hr_askforleave.ask_temp='0' ");
		if (searchType.equals("员工姓名")) {
			sql.append(" and e_name like ?");
		}
		if (searchType.equals("员工编号")) {
			sql.append(" and e_number like ?");
		}
		sql.append(" order by hr_employee.e_number");
		sql.append(" limit ?,?");
		List<AskForLeave> afls = new ArrayList<AskForLeave>();
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			if (searchType.equals("查找所有")) {
				pstmt.setInt(1, start);
				pstmt.setInt(2, pageSize);
			} else {
				pstmt.setString(1, "%"+searchContent+"%");
				pstmt.setInt(2, start);
				pstmt.setInt(3, pageSize);
			}
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				AskForLeave afl = new AskForLeave();
				afl.setE_id(rs.getInt("e_id"));
				afl.setAsk_id(rs.getInt("ask_id"));
				afl.setE_number(rs.getString("e_number"));
				afl.setE_name(rs.getString("e_name"));
				afl.setAsk_startdate(rs.getString("ask_startdate"));
				afl.setAsk_enddate(rs.getString("ask_enddate"));
				afl.setAsk_reason(rs.getString("ask_reason"));
				short state = rs.getShort("ask_state");
				if (state == 0) {
					afl.setAsk_state("申请中");
				}else if (state == 1){
					afl.setAsk_state("未销假");
				} else {
					afl.setAsk_state("已销假");
				}
				afls.add(afl);
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return afls;
	}

	@Override
	public int selectApplyAflCount(String searchType, String searchContent) {
		StringBuffer sql = new StringBuffer("select count(ask_id) afl_count from hr_askforleave,hr_employee where ");
		sql.append(" hr_askforleave.e_id=hr_employee.e_id and hr_askforleave.ask_temp='0' ");
		if (searchType.equals("员工姓名")) {
			sql.append(" and e_name like ?");
		}
		if (searchType.equals("员工编号")) {
			sql.append(" and e_number like ?");
		}
		Connection conn = JDBCUtil.getConnection();
		int afl_count = 0;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			if (!searchType.equals("查找所有")){
				pstmt.setString(1, "%"+searchContent+"%");
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				afl_count = rs.getInt("afl_count");
			}
			JDBCUtil.closeJDBC(pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return afl_count;
	}

}
